# sqlalchemy模块


from unicodedata import name
from sqlalchemy import or_
import imp
from sqlalchemy.engine import result
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import create_engine, Column, Integer, String
from sqlalchemy.orm import session, sessionmaker
from sqlalchemy.sql.operators import ilike_op
from test.libregrtest.utils import count


# create_engine 建立连接引擎
engine = create_engine(
    "mysql+pymysql://root:123456@127.0.0.1:3306/test?charset=utf8mb4", echo=True)
Base = declarative_base(engine)  # 创建SQLORM基类,Base是declarative_base的实例化对象
Session = sessionmaker(bind=engine)  # 创建Session类型:
session = Session()  # 实例化session对象
# 创建表


class Student(Base):
    __tablename__ = 'student'
    id = Column(Integer, primary_key=True, autoincrement=True)
    name = Column(String(50))
    age = Column(Integer)
    sex = Column(String(10))


Base.metadata.create_all()  # 将模型映射到数据库中,创建表，如果表存在则忽视


# 添加数据
student = Student(name='Tony', age=18, sex='male')  # 创建一个student对象
session.add(student)  # 添加到session
session.commit()  # 提交到数据库
# 批量添加
session.add_all([
    Student(name='Daniel', age=5, sex='male'),
    Student(name='Easion', age=3, sex='male')
])
session.commit()

# 查询数据
item_list = session.query(Student).all()
# print(item_list)
for item in item_list:
    print(item.id, item.name, item.age, item.sex)
# 指定查询列
item_list = session.query(Student.name).all()
print(item_list)
# 获取返回数据的第一行
item_list = session.query(Student.name).first()
print(item_list)
# 使用filter()方法进行筛选过滤
item_list = session.query(Student.name).filter(Student.age < 18).all()
print(item_list)
# filter_by() 接收的是关键字参数
item_list = session.query(Student.name).filter_by(name='Daniel').first()
print(item_list)

# 使用order_by()进行排序
item_list = session.query(Student.name, Student.age).order_by(
    Student.age.desc()).all()  # desc()表示倒序 asc()表示倒序
print(item_list)
# 多个查询条件（and和or）
# 默认为and, 在filter()中用,分隔多个条件表示and
item_list = session.query(Student.name, Student.age, Student.sex).filter(
    Student.age > 30, Student.sex == 'female'
).all()
print(item_list)
# 使用or_连接多个条件
item_list = session.query(Student.name, Student.age, Student.sex).filter(
    or_(Student.age > 30, Student.sex == 'female')
).all()
print(item_list)

# equal/like/in
item_list = session.query(Student.name, Student.age, Student.sex).filter(
    Student.age == 3
).all()
print(item_list)
item_list = session.query(Student.name, Student.age, Student.sex).filter(
    Student.age != 18
).all()
print(item_list)
# like
item_list = session.query(Student.name, Student.age, Student.sex).filter(
    Student.name.like('%ni%')
).all()
print(item_list)
# in
item_list = session.query(Student.name, Student.age, Student.sex).filter(
    Student.age.in_([16, 20])
).all()
print(item_list)
# Not in 使用波浪号~ 表示非
item_list = session.query(Student.name, Student.age, Student.sex).filter(
    ~Student.age.in_([16, 20])
).all()
print(item_list)
# between
item_list = session.query(Student.name, Student.age, Student.sex).filter(
    Student.age.between(16, 20)).all()
print(item_list)

# count计算个数
count = session.query(Student).count()
print(count)
# 切片
item_list = session.query(Student.name).all()[:2]
print(item_list)

# 修改数据
# 修改数据可以使用update()方法，update完成后记得执行session.commit()
# 修改Tony的age为22
session.query(Student).filter(Student.name == 'Tony').update({'age': 22})
session.commit()
item = session.query(Student.name, Student.age).filter(
    Student.name == 'Tony').first()
print(item)

# 删除数据
# 删除数据使用delete()方法，同样也需要执行session.commit()提交事务
# 删除名称为Tony的数据
session.query(Student).filter(Student.name == 'Tony').delete()
session.commit()
item_list = session.query(Student.name, Student.age).all()
print(item_list)

# 回滚 在 commit() 之前，对实例对象的属性所做的更改，可以进行回滚。回到更改之前。
session.rollback()
session.close()

# 删除表
Base.metadata.drop_all(engine)# all table are deleted
# Student.__table__.drop()
# Student.__table__.drop(engine)
# 删除表原生SQL
# sql= 'Drop table if exists student;'
# result = engine.execute(sql)
item_list = session.query(Student.name).all()
print(item_list)
